USE [NANS21V]
GO

/****** Object:  UserDefinedFunction [dbo].[CommentCodeConvert]    Script Date: 04/24/2014 13:28:07 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommentCodeConvert]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[CommentCodeConvert]
GO

USE [NANS21V]
GO

/****** Object:  UserDefinedFunction [dbo].[CommentCodeConvert]    Script Date: 04/24/2014 13:28:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[CommentCodeConvert]
(
	@MOJI varchar(1000)
	,@SYUKKETU int
	--,@MODORI varchar(1000) OUTPUT
)
RETURNS VARCHAR(1000)
AS
BEGIN

  Declare
     @i int
    ,@Count int
    ,@r1 int
    ,@r2  varchar(100)
    ,@MODORI varchar(1000)
    ,@DELIMITER varchar(1)
 
--SET NOCOUNT ON /*select 結果を出力しない文*/
  Declare
    @wktbl table(
          /*ID int identity(1,1), --ループ用に1から順番に番号を付ける*/
         a1 int
        ,a2 varchar(100)
    );
         
/*aplit*/


  INSERT INTO @wktbl (a1, a2)--a1=index a2=検索したいID
    SELECT * FROM dbo.Split(@MOJI, ',');


--DECLARE @ret varchar(1000);
  SET @MODORI ='';
  SET @DELIMITER=',';--固定に
  SELECT @Count = Count(*) FROM @wktbl
  Set @i = 1
  WHILE @i <= @Count
  BEGIN
    --対象ID取得
    SELECT @r1 = a2 FROM @wktbl WHERE a1 = @i
    
    --コメントテーブルから取り出し
    
    -- 2019/04/11 コメントＮＯ＝””で取得するとコメントＮＯ＝０を取得しちゃうのではじく
	IF (@r1 = '')
    BEGIN 
		SET @i = @i + 1;
		continue
	END
    
    SELECT @r2 = COMMENTRYAKU FROM TBL_COMMENT WHERE COMMENTNO = @r1
    
    IF 1 < @i
	BEGIN
	    set @MODORI =@MODORI+',';
	END--if
    
    IF @r2 IS NULL
	BEGIN
	    set @r2 ='';
	END--if
	
    set @MODORI = @MODORI + @r2;
    set @i = @i + 1;
  END --while  

  --出欠が2ならDNS文字列を追加
  if @SYUKKETU = 2
  BEGIN
    if @MODORI !=''
    BEGIN
      set @MODORI =@MODORI+',';
    END
    set @MODORI =@MODORI+'DNS';
  END

	RETURN @MODORI
END

GO

